package Pet.Management.service.impl;

import Pet.Management.domain.dto.AgeDistributionDTO;
import Pet.Management.domain.dto.GenderDistributionDTO;
import Pet.Management.domain.dto.PetTypeDistributionDTO;
import Pet.Management.domain.dto.RegTrendDTO;
import Pet.Management.service.AnalysisService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.Period;
import java.util.*;

@Service
public class AnalysisServiceImpl implements AnalysisService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public GenderDistributionDTO getGenderDistribution() {
        String sql = "SELECT " +
                    "SUM(CASE WHEN sex = 0 THEN 1 ELSE 0 END) as male_count, " +
                    "SUM(CASE WHEN sex = 1 THEN 1 ELSE 0 END) as female_count, " +
                    "SUM(CASE WHEN sex IS NULL THEN 1 ELSE 0 END) as other_count " +
                    "FROM sys_user";

        return jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
            GenderDistributionDTO dto = new GenderDistributionDTO();
            dto.setMaleCount(rs.getInt("male_count"));
            dto.setFemaleCount(rs.getInt("female_count"));
            dto.setOtherCount(rs.getInt("other_count"));
            return dto;
        });
    }

    @Override
    public AgeDistributionDTO getAgeDistribution() {
        // 获取所有用户的身份证号
        String sql = "SELECT id_card FROM sys_user WHERE id_card IS NOT NULL AND LENGTH(id_card) = 18";
        List<String> idCards = jdbcTemplate.queryForList(sql, String.class);

        Map<String, Integer> ageGroups = new HashMap<>();
        ageGroups.put("0-18", 0);
        ageGroups.put("19-25", 0);
        ageGroups.put("26-35", 0);
        ageGroups.put("36-45", 0);
        ageGroups.put("46+", 0);

        double totalAge = 0;
        int validUserCount = 0;

        LocalDate now = LocalDate.now();

        for (String idCard : idCards) {
            try {
                // 从身份证号提取出生日期（格式：YYYYMMDD）
                String birthDateStr = idCard.substring(6, 14);
                LocalDate birthDate = LocalDate.of(
                    Integer.parseInt(birthDateStr.substring(0, 4)),
                    Integer.parseInt(birthDateStr.substring(4, 6)),
                    Integer.parseInt(birthDateStr.substring(6, 8))
                );

                int age = Period.between(birthDate, now).getYears();
                totalAge += age;
                validUserCount++;

                // 根据年龄分组
                if (age <= 18) {
                    ageGroups.put("0-18", ageGroups.get("0-18") + 1);
                } else if (age <= 25) {
                    ageGroups.put("19-25", ageGroups.get("19-25") + 1);
                } else if (age <= 35) {
                    ageGroups.put("26-35", ageGroups.get("26-35") + 1);
                } else if (age <= 45) {
                    ageGroups.put("36-45", ageGroups.get("36-45") + 1);
                } else {
                    ageGroups.put("46+", ageGroups.get("46+") + 1);
                }
            } catch (Exception e) {
                // 忽略无效的身份证号
                continue;
            }
        }

        AgeDistributionDTO dto = new AgeDistributionDTO();
        dto.setAgeGroups(ageGroups);
        dto.setAverageAge(validUserCount > 0 ? totalAge / validUserCount : 0.0);
        return dto;
    }

    @Override
    public PetTypeDistributionDTO getPetTypeDistribution() {
        // 统计宠物类型分布
        String sql = "SELECT pet_type, COUNT(*) as count " +
                    "FROM pet_demand " +
                    "WHERE del_flag = false " +  // 只统计未删除的记录
                    "GROUP BY pet_type";

        List<Map<String, Object>> results = jdbcTemplate.queryForList(sql);
        
        Map<String, Integer> petTypes = new HashMap<>();
        int totalPets = 0;

        for (Map<String, Object> row : results) {
            String petType = (String) row.get("pet_type");
            int count = ((Number) row.get("count")).intValue();
            petTypes.put(petType, count);
            totalPets += count;
        }

        PetTypeDistributionDTO dto = new PetTypeDistributionDTO();
        dto.setPetTypes(petTypes);
        dto.setTotalPets(totalPets);
        
        return dto;
    }

    @Override
    public RegTrendDTO getRegTrend(String type) {
        RegTrendDTO dto = new RegTrendDTO();
        dto.setType(type);

        String sql;
        
        // 根据不同的统计类型设置SQL，使用MySQL兼容的日期函数
        switch (type) {
            case "day":
                sql = "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as time_point, " +
                      "COUNT(*) as count " +
                      "FROM sys_user " +
                      "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') " +
                      "ORDER BY time_point ASC";
                break;
            case "month":
                sql = "SELECT DATE_FORMAT(create_time, '%Y-%m') as time_point, " +
                      "COUNT(*) as count " +
                      "FROM sys_user " +
                      "GROUP BY DATE_FORMAT(create_time, '%Y-%m') " +
                      "ORDER BY time_point ASC";
                break;
            case "year":
                sql = "SELECT DATE_FORMAT(create_time, '%Y') as time_point, " +
                      "COUNT(*) as count " +
                      "FROM sys_user " +
                      "GROUP BY DATE_FORMAT(create_time, '%Y') " +
                      "ORDER BY time_point ASC";
                break;
            default:
                throw new IllegalArgumentException("Invalid trend type: " + type);
        }

        List<Map<String, Object>> results = jdbcTemplate.queryForList(sql);

        List<String> timePoints = new ArrayList<>();
        List<Integer> counts = new ArrayList<>();
        int totalCount = 0;

        for (Map<String, Object> row : results) {
            timePoints.add(row.get("time_point").toString());
            int count = ((Number) row.get("count")).intValue();
            counts.add(count);
            totalCount += count;
        }

        dto.setTimePoints(timePoints);
        dto.setCounts(counts);
        dto.setTotalRegistrations(totalCount);

        return dto;
    }
} 